package com.css.fxfzypg.modules.model.repository.Impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.entity.PbeEntity;
import com.css.fxfzypg.modules.model.entity.Casualty;
import com.css.fxfzypg.modules.model.entity.DataPbeInjuries;
import com.css.fxfzypg.modules.model.entity.DataPbeInjuriesVo;
import com.css.fxfzypg.modules.model.entity.DataPbeIntStrArea;
import com.css.fxfzypg.modules.model.repository.CasualtyRepository;
import com.css.fxfzypg.modules.model.to.CasualtyTO;
import com.css.fxfzypg.modules.preassesstaskmanage.entity.PreAssessTaskEntity;
import com.css.fxfzypg.modules.preassesstaskmanage.repository.PreAssessTaskRepository;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.JdbcUtil;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import com.fasterxml.jackson.databind.deser.impl.BeanPropertyMap;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

/**
 * @author Dbj
 * @date 2022年09月27日  17:18
 */
@Repository
public class CasualtyRepositoryImpl implements CasualtyRepository {

    @Resource
    private SUserService sUserService;

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private PreAssessTaskRepository preAssessTaskRepository;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    private SysAreaService areaService;

    public static final int BATCH_SIZE = 1000;

    @Override
    public Map<String, Object> findPage(CasualtyTO casualtyTO, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from casualty_calculation ");
        StringBuilder whereSql = new StringBuilder(" where is_valid ='" + YNEnum.Y.toString() + "'");
        String orderSql = "  order by is_default desc,province_code";
        StringBuilder countSql = new StringBuilder("select count(*) from casualty_calculation ");
        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        if (!casualtyTO.getNation()) {
            whereSql.append(" and (org_name = '系统默认' or province='").append(sUser.getProvinceId()).append("')");
        }
        if (!PlatformObjectUtils.isEmpty(casualtyTO.getModelName())) {
            whereSql.append(" and model_name like '%" + casualtyTO.getModelName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(casualtyTO.getProvince())) {
            if ("cd726776c7de41e4a41dcdb3d90b2806".equals(casualtyTO.getProvince()) || "225ac763ab714524b87d95140a93a4a5".equals(casualtyTO.getProvince()) || "8a3f7d3f78ad456f92f487a979091c7f".equals(casualtyTO.getProvince()) || "afcbf07008a34c2e85917cfd34008033".equals(casualtyTO.getProvince())) {
                //拿到北京上海天津重庆四省的省id
                String pid = areaService.getParentId(casualtyTO.getProvince());
                casualtyTO.setProvince(pid);
            }
            whereSql.append(" and province='").append(casualtyTO.getProvince()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(casualtyTO.getCity())) {
            whereSql.append(" and (position('").append(casualtyTO.getCity()).append("' in city) > 0 ");
            whereSql.append(" or city='全部')");
        }
        String limitSql = " limit " + pageSize + " offset " + (curPage - 1) * pageSize;
        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        countSql.append(whereSql);
        List<Casualty> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(Casualty.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public Map<String, Object> findPageForXczh(CasualtyTO casualtyTO, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from casualty_calculation ");
        StringBuilder whereSql = new StringBuilder(" where is_valid ='" + YNEnum.Y.toString() + "'");
        String orderSql = "  order by is_default desc,province_code";
        StringBuilder countSql = new StringBuilder("select count(*) from casualty_calculation ");
        String user = sUserService.getSUser(casualtyTO.getUserId());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        //whereSql.append(" and (org_name = '系统默认' or province='").append(sUser.getProvinceId()).append("')");
        if (!PlatformObjectUtils.isEmpty(casualtyTO.getModelName())) {
            whereSql.append(" and model_name like '%" + casualtyTO.getModelName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(casualtyTO.getProvince())) {
            if ("cd726776c7de41e4a41dcdb3d90b2806".equals(casualtyTO.getProvince()) || "225ac763ab714524b87d95140a93a4a5".equals(casualtyTO.getProvince()) || "8a3f7d3f78ad456f92f487a979091c7f".equals(casualtyTO.getProvince()) || "afcbf07008a34c2e85917cfd34008033".equals(casualtyTO.getProvince())) {
                //拿到北京上海天津重庆四省的省id
                String pid = areaService.getParentId(casualtyTO.getProvince());
                casualtyTO.setProvince(pid);
            }
            whereSql.append(" and province='").append(casualtyTO.getProvince()).append("'");
        }
        if (!PlatformObjectUtils.isEmpty(casualtyTO.getCity())) {
            whereSql.append(" and (position('").append(casualtyTO.getCity()).append("' in city) > 0 ");
            whereSql.append(" or city='全部')");
        }
        String limitSql = " limit " + pageSize + " offset " + (curPage - 1) * pageSize;
        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        countSql.append(whereSql);
        List<Casualty> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(Casualty.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public Casualty getModelById(String id) {
        String sql = "select * from casualty_calculation where is_valid ='" + YNEnum.Y.toString() + "' and id='" + id + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Casualty.class));
    }

    @Override
    public Casualty getDefaultModel() {
        String sql = "select * from casualty_calculation where is_valid ='" + YNEnum.Y.toString() +
                "' and is_default='" + YNEnum.Y.toString() + "' limit 1";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Casualty.class));
    }

    @Override
    public List<Casualty> getByDivision(String province, String city, int isNa) {
        String sql = "select * from casualty_calculation where is_valid ='" + YNEnum.Y.toString() + "' and province='" + province + "'";
        if (PlatformObjectUtils.isNotEmpty(city)) {
            sql = sql + " and city='" + city + "'";
        }
        sql = sql + " order by create_time desc ";
        if (isNa == 0) {
            sql = sql + " limit 1 ";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Casualty.class));
    }

    @Override
    public void save(Casualty casualty) {
        jdbcTemplate.update("insert into casualty_calculation(id,model_name,smrs_number1,smrs_number2,injuries,is_default,province,city,create_user_id,create_user_name,create_time,org_id,org_name,is_valid,death_number,province_code) " +
                        "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", casualty.getId(), casualty.getModelName(), casualty.getSmrsNumber1(), casualty.getSmrsNumber2(),
                casualty.getInjuries(), casualty.getIsDefault(), casualty.getProvince(), casualty.getCity(), casualty.getCreateUserId(), casualty.getCreateUserName(), casualty.getCreateTime(),
                casualty.getOrgId(), casualty.getOrgName(), casualty.getIsValid(), casualty.getDeathNumber(), casualty.getProvinceCode());
    }

    @Override
    public void update(Casualty casualty) {
        jdbcTemplate.update("update casualty_calculation set model_name=?,smrs_number1=?,smrs_number2=?,injuries=?," +
                        "death_number=?,update_user_id=?,update_time=? where id=?", casualty.getModelName(), casualty.getSmrsNumber1(), casualty.getSmrsNumber2(),
                casualty.getInjuries(), casualty.getDeathNumber(), casualty.getUpdateUserId(), casualty.getUpdateTime(), casualty.getId());
    }

    @Override
    public void delete(String userId, Date updateTime, String id) {
        jdbcTemplate.update("update casualty_calculation set is_valid=?,update_user_id=?,update_time=? where id=?", YNEnum.N.toString(), userId, updateTime, id);
    }

    @Override
    public Casualty getDefaultModel(String taskId) {
        PreAssessTaskEntity preAssessTaskEntity = preAssessTaskRepository.findById(taskId);
        if (PlatformObjectUtils.isNotEmpty(preAssessTaskEntity)) {
            String taskDistrict = preAssessTaskEntity.getTaskDistrict();
            if (StringUtils.isNotBlank(taskDistrict)) {
                List<String> areaList = Arrays.asList(taskDistrict.split("、"));
                try {
                    //山东省-济宁市-金乡县
                    if (!PlatformObjectUtils.isEmpty(areaList)) {
                        for (String area : areaList) {
                            String idConcat = getAreaIdByName(area);
                            if (StringUtils.isNotBlank(idConcat)) {
                                Casualty casualty = getFirstCasualty(idConcat);
                                if (casualty != null) {
                                    return casualty;
                                }
                            }
                        }
                    } else {
                        return null;
                    }
                } catch (Exception e) {
                    throw new RuntimeException("模型查询失败");
                }
            }
        }
        return null;
    }

    /**
     * 返回第一个符合条件的模型信息
     */
    private Casualty getFirstCasualty(String idConcat) {
        String[] idSplit = idConcat.split("_");
        String provinceId = idSplit[0];
        String cityId = idSplit[1];
        //查询该省市下的人员伤亡模型
        List<Casualty> casualties = getCasualtyByProvinceAndCity(provinceId, cityId);
        if (casualties.size() > 0) {
            return casualties.get(0);
        }
        return null;
    }

    /**
     * 查询指定省和市下的人员伤亡的模型信息
     */
    private List<Casualty> getCasualtyByProvinceAndCity(String provinceId, String cityId) {
        StringBuilder builder = new StringBuilder("select * from casualty_calculation where is_valid ='" + YNEnum.Y.toString() + "'");
        builder.append(" and province='").append(provinceId).append("'");
        builder.append(
                " and (position('").append(cityId).append("' in city) > 0 or city='全部')");
        builder.append(" order by create_time desc");
        return jdbcTemplate.query(builder.toString(), new BeanPropertyRowMapper<>(Casualty.class));
    }

    /**
     * 查询指定省和市名称对应的的id 用_分割
     */
    @Override
    public String getAreaIdByName(String area) throws UnsupportedEncodingException {
        String[] split = area.split("-");
        //省
        String province = URLEncoder.encode(split[0], "UTF-8");
        String city = "";
        if (split.length > 1) {
            //市
            city = URLEncoder.encode(split[1], "UTF-8");
            //根据区域名称获取id
        } else {
            city = URLEncoder.encode("-1", "UTF-8");
        }
        //区县并不关注填充-1只是为了指定方法接收并不为空
        String country = URLEncoder.encode("-1", "UTF-8");
        return areaService.getIdByCondition(province, city, country);
    }

    @Override
    public List<PbeEntity> getAllPbeByTaskId(String taskId, int k,String range) {
    /*String sql =
        "SELECT *,st_astext(geom) geomText FROM data_pbe t "
            + "WHERE ST_Intersects(t.geom,(select st_union(a.geom,(select b.geom from ypg_preassess_task b where id='"+taskId+"')) " +
                "as geom from (select st_union(c.geom) as geom from setting_seismic_influenceflied c " +
                "where c.task_id='"+taskId+"') a)) and isdelete='0'";*/
        String sql = "SELECT *,st_astext(geom) geomText FROM data_pbe t ";
        if (k == 0 && range.equals("0")) {
            sql = sql + "where ST_Intersects(T.geom,(select geom from ypg_preassess_task where id='" + taskId + "')) ";
        } else if (k == 1 && range.equals("0")) {
            sql = sql + "where ST_Intersects (geom," +
                    " (SELECT st_union ( n.geom ) AS geom FROM setting_seismic_influenceflied n WHERE n.task_id = '" + taskId + "')" +
                    ")='t' " +
                    " AND ST_Intersects (geom," +
                    "  (SELECT geom FROM ypg_preassess_task WHERE id = '" + taskId + "')" +
                    ")='f' ";
        }else if (range.equals("1")) {
            sql = sql + "where ST_Intersects (geom," +
                    " (SELECT st_union ( n.geom ) AS geom FROM setting_seismic_influenceflied n WHERE n.task_id = '" + taskId + "')" +
                    ")='t' " +
                    " AND ST_Intersects (geom," +
                    "  (SELECT geom FROM ypg_preassess_task WHERE id = '" + taskId + "')" +
                    ")='t' ";
        }
        sql = sql + "and isdelete='0' order by province,city,county";
        List<PbeEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PbeEntity.class));
        return list;
    }

    @Override
    public void saveDataPbeInjuries(DataPbeInjuries pbeInjuries) {
        jdbcTemplate.update("insert into data_pbe_injuries(id,pbe_id,population,coveredarea,death_num,injuries_num,death,injuries,dz_intentity,task_id,geom_text) " +
                        "values(?,?,?,?,?,?,?,?,?,?,?)", pbeInjuries.getId(), pbeInjuries.getPbeId(), pbeInjuries.getPopulation(), pbeInjuries.getCoveredarea(),
                pbeInjuries.getDeathNum(), pbeInjuries.getInjuriesNum(), pbeInjuries.getDeath(), pbeInjuries.getInjuries(), pbeInjuries.getDzIntentity(), pbeInjuries.getTaskId(), pbeInjuries.getGeomText());
    }

    @Override
    public void saveDataPbeIntStrArea(DataPbeIntStrArea entity) {
        jdbcTemplate.update("insert into data_pbe_int_str_area(id,pbe_id,dz_intensity,h_area,steel_area,c_area," +
                        "b_area,bm_area,s_area,w_area,st_area,o_area,destroy,population,covered_area,townorcontry," +
                        "province,city,county,town,dhp_house_tatio,task_id,geom_text,type1) " +
                        " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                entity.getId(), entity.getPbeId(), entity.getDzIntensity(), entity.getHArea(), entity.getSteelArea(),
                entity.getCArea(), entity.getBArea(), entity.getBmArea(), entity.getSArea(), entity.getWArea(),
                entity.getStArea(), entity.getOArea(), entity.getDestroy(), entity.getPopulation(), entity.getCoveredArea(),
                entity.getTownorcontry(), entity.getProvince(), entity.getCity(), entity.getCounty(), entity.getTown(),
                entity.getDhpHouseTatio(), entity.getTaskId(), entity.getGeomText(), entity.getType1());
    }

    @Override
    public void deleteByTaskId(String taskId) {
        //jdbcTemplate.update("delete from  data_pbe_injuries  where task_id=?", taskId);
        String sql = "drop table data_pbe_injuries_" + taskId;
        JdbcUtil.executeSql(sql);
    }

    @Override
    public List<Map<String, Object>> getPbeInjuriesByTaskId(String taskId,String range) {
        String sql="";
        if ("0".equals(range)){
            sql = "select a.pbe_id pbeId,st_area(st_intersection(ST_MakeValid(st_geometryfromtext(a.geom_text,4490)), b.geom)) as area,a.death_num deathNum," +
                    "a.injuries_num injuriesNum,b.sort sort,b.earthquake_level earthquakeLevel,b.angle angle,b.source_earthquake_id earthquakeId" +
                    " from data_pbe_injuries a left join (select b.geom,b.task_id,b.dz_intensity,c.fault_num sort,c.earthquake_level,c.angle,b.source_earthquake_id " +
                    " from setting_seismic_details c left join setting_seismic_influenceflied b on c.id=b.source_earthquake_id" +
                    " where b.task_id='" + taskId + "' and c.is_delete='0') b on a.task_id=b.task_id " +
                    " where a.dz_intentity=b.dz_intensity and st_intersects(st_geometryfromtext(a.geom_text,4490),b.geom)";
        }else {
            sql = "select a.pbe_id pbeId,st_area(st_intersection(ST_MakeValid(st_geometryfromtext(a.geom_text,4490)), b.geom)) as area,a.death_num deathNum," +
                    "a.injuries_num injuriesNum,b.sort sort,b.earthquake_level earthquakeLevel,b.angle angle,b.source_earthquake_id earthquakeId" +
                    " from data_pbe_injuries a left join (select b.geom,b.task_id,b.dz_intensity,c.fault_num sort,c.earthquake_level,c.angle,b.source_earthquake_id " +
                    " from setting_seismic_details c left join setting_seismic_influenceflied b on c.id=b.source_earthquake_id" +
                    " where b.task_id='" + taskId + "' and c.is_delete='0') b on a.task_id=b.task_id " +
                    " where a.dz_intentity=b.dz_intensity and st_intersects(st_geometryfromtext(a.geom_text,4490),b.geom)  AND st_intersects ( st_geometryfromtext ( A.geom_text, 4490 ), (select geom FROM ypg_preassess_task where id='"+taskId+"') )";
        }
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<DataPbeInjuries> getPbeInjuries(String taskId, String dzIntensity) {
        /*String sql =
                "SELECT *,ST_AsText(st_intersection((select st_union(a.geom,(select b.geom from ypg_preassess_task b " +
                        "where id='"+taskId+"')) as geom from " +
                        "(select st_union(c.geom) as geom from setting_seismic_influenceflied c where c.task_id='"+taskId+"') a),st_geometryfromtext(s.geom_text,4490))) geomText " +
                        "FROM (SELECT * FROM data_pbe_injuries t WHERE ST_Intersects(st_geometryfromtext(t.geom_text,4490),(select st_union(a.geom,(select b.geom from ypg_preassess_task b where id='"+taskId+"')) as geom " +
                        "from (select st_union(c.geom) as geom from setting_seismic_influenceflied c where c.task_id='"+taskId+"') a)) and dz_intentity='"+dzIntensity+"' and task_id='"+taskId+"') s";*/
        String sql = "select * from data_pbe_injuries " +
                "where task_id='" + taskId + "' and dz_intentity='" + dzIntensity + "'";
        List<DataPbeInjuries> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DataPbeInjuries.class));
        return list == null ? new ArrayList<>(0) : list;
    }

    @Override
    public List<DataPbeInjuries> getTaskPbe(String taskId) {
        String sql = "select * from data_pbe_injuries where task_id='" + taskId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DataPbeInjuries.class));
    }

    @Override
    public List<DataPbeInjuriesVo> getTaskPbeVo(String taskId) {
        String sql = "select concat_ws('-',pbe_id,dz_intentity) as id,death_num from data_pbe_injuries where task_id='" + taskId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DataPbeInjuriesVo.class));
    }

    @Override
    public Integer getTaskPbeCount(String taskId) {
        String sql = "select count(1) from data_pbe_injuries where task_id='" + taskId + "'";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    @Override
    public void deleteDataPbeIntStrAreaByTaskId(String taskId) {
        String sql = "drop table data_pbe_int_str_area_" + taskId;
        //jdbcTemplate.execute(sql);
        JdbcUtil.executeSql(sql);
        //jdbcTemplate.update("delete from  data_pbe_int_str_area  where task_id=?", taskId);
    }

    @Override
    public List<DataPbeIntStrArea> getDataPbeIntStrAreaByTaskId(String taskId, String destroy) {
        String sql = "select * from data_pbe_int_str_area where task_id='" + taskId + "'";
        if (PlatformObjectUtils.isNotEmpty(destroy)) {
            sql = sql + " and destroy='" + destroy + "'";
        }
        sql = sql + " order by cast(type1 as int),pbe_id,cast(dz_intensity as int),destroy";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DataPbeIntStrArea.class));
    }

    @Override
    public Integer getDataPbeIntStrAreaCountByTaskId(String taskId, String destroy) {
        String sql = "select count(1) from data_pbe_int_str_area where task_id='" + taskId + "'";
        if (PlatformObjectUtils.isNotEmpty(destroy)) {
            sql = sql + " and destroy='" + destroy + "'";
        }
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    @Override
    public List<DataPbeIntStrArea> getLimitListByTaskId(String taskId, String destroy, int fromIndex, int pageSize) {
        String sql = "select * from data_pbe_int_str_area where task_id='" + taskId + "'";
        if (PlatformObjectUtils.isNotEmpty(destroy)) {
            sql = sql + " and destroy='" + destroy + "'";
        }
        sql = sql + " order by cast(type1 as int),pbe_id,cast(dz_intensity as int),destroy" + " limit " + pageSize + " offset " + fromIndex;
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DataPbeIntStrArea.class));
    }

    @Override
    public DataPbeIntStrArea findByTaskIdLimit(String taskId) {
        String sql = "select * from data_pbe_int_str_area where task_id='" + taskId + "' limit 1";
        List<DataPbeIntStrArea> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DataPbeIntStrArea.class));
        return query.size() < 1 ? null : query.get(0);
    }

    @Override
    public DataPbeInjuries findByTaskIdLimit2(String taskId) {
        String sql = "select * from data_pbe_injuries where task_id='" + taskId + "' limit 1";
        List<DataPbeInjuries> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DataPbeInjuries.class));
        return query.size() < 1 ? null : query.get(0);
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public int batchupdate(List<DataPbeIntStrArea> pbeIntStrAreaList) {
        String sql = "insert into data_pbe_int_str_area(id,pbe_id,dz_intensity,h_area,steel_area,c_area," +
                "b_area,bm_area,s_area,w_area,st_area,o_area,destroy,population,covered_area,townorcontry," +
                "province,city,county,town,dhp_house_tatio,task_id,geom_text,type1) " +
                " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        int[] updateCountArray = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, pbeIntStrAreaList.get(i).getId());
                ps.setString(2, pbeIntStrAreaList.get(i).getPbeId());
                ps.setString(3, pbeIntStrAreaList.get(i).getDzIntensity());
                ps.setFloat(4, pbeIntStrAreaList.get(i).getHArea().floatValue());
                ps.setFloat(5, pbeIntStrAreaList.get(i).getSteelArea().floatValue());
                ps.setFloat(6, pbeIntStrAreaList.get(i).getCArea().floatValue());
                ps.setFloat(7, pbeIntStrAreaList.get(i).getBArea().floatValue());
                ps.setFloat(8, pbeIntStrAreaList.get(i).getBmArea().floatValue());
                ps.setFloat(9, pbeIntStrAreaList.get(i).getSArea().floatValue());
                ps.setFloat(10, pbeIntStrAreaList.get(i).getWArea().floatValue());
                ps.setFloat(11, pbeIntStrAreaList.get(i).getStArea().floatValue());
                ps.setFloat(12, pbeIntStrAreaList.get(i).getOArea().floatValue());
                ps.setString(13, pbeIntStrAreaList.get(i).getDestroy());
                ps.setFloat(14, pbeIntStrAreaList.get(i).getPopulation().floatValue());
                ps.setFloat(15, pbeIntStrAreaList.get(i).getCoveredArea().floatValue());
                ps.setString(16, pbeIntStrAreaList.get(i).getTownorcontry());
                ps.setString(17, pbeIntStrAreaList.get(i).getProvince());
                ps.setString(18, pbeIntStrAreaList.get(i).getCity());
                ps.setString(19, pbeIntStrAreaList.get(i).getCounty());
                ps.setString(20, pbeIntStrAreaList.get(i).getTown());
                ps.setFloat(21, pbeIntStrAreaList.get(i).getDhpHouseTatio().floatValue());
                ps.setString(22, pbeIntStrAreaList.get(i).getTaskId());
                ps.setString(23, pbeIntStrAreaList.get(i).getGeomText());
                ps.setString(24, pbeIntStrAreaList.get(i).getType1());
            }

            @Override
            public int getBatchSize() {
                return pbeIntStrAreaList.size();
            }
        });
        int sumInsertedCount = 0;
        for (int a : updateCountArray) {
            sumInsertedCount += a;
        }
        return sumInsertedCount;
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public int savepbeInjuriesList(List<DataPbeInjuries> list) {
        String sql = "insert into data_pbe_injuries(id,pbe_id,population,coveredarea,death_num,injuries_num,death," +
                "injuries,dz_intentity,task_id,geom_text) " +
                "values(?,?,?,?,?,?,?,?,?,?,?)";
        int[] updateCountArray = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, list.get(i).getId());
                ps.setString(2, list.get(i).getPbeId());
                ps.setInt(3, list.get(i).getPopulation());
                ps.setFloat(4, list.get(i).getCoveredarea().floatValue());
                ps.setFloat(5, list.get(i).getDeathNum().floatValue());
                ps.setFloat(6, list.get(i).getInjuriesNum().floatValue());
                ps.setFloat(7, list.get(i).getDeath().floatValue());
                ps.setFloat(8, list.get(i).getInjuries().floatValue());
                ps.setString(9, list.get(i).getDzIntentity());
                ps.setString(10, list.get(i).getTaskId());
                ps.setString(11, list.get(i).getGeomText());
            }

            @Override
            public int getBatchSize() {
                return list.size();
            }
        });
        int sumInsertedCount = 0;
        for (int a : updateCountArray) {
            sumInsertedCount += a;
        }
        return sumInsertedCount;
    }

    @Override
    public void createFqRel(String taskId) {
        String createSql = "create table data_pbe_int_str_area_" + taskId +
                " PARTITION OF data_pbe_int_str_area for values in ('" + taskId + "')";
        //jdbcTemplate.execute(createSql);
        JdbcUtil.executeSql(createSql);
    }

    @Override
    public void createInjuriesFqRel(String taskId) {
        String createSql = "create table data_pbe_injuries_" + taskId +
                " PARTITION OF data_pbe_injuries for values in ('" + taskId + "')";
        //jdbcTemplate.execute(createSql);
        JdbcUtil.executeSql(createSql);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from casualty_calculation where province = '" + province + "'  and is_valid ='1' ", Integer.class);
    }

    @Override
    public Integer countByYearPrefixLike(String taskYear, String prov) {
            return jdbcTemplate.queryForObject("SELECT count(*) FROM casualty_calculation where (TO_CHAR(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE '" + taskYear + "%' or TO_CHAR(update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE '" + taskYear + "%') and province = '" + prov + "'  and is_valid ='1' ", Integer.class);
    }


}
